package database;

import java.sql.*;
import java.util.ArrayList;

import simulation.obj.Trajectory;

public class QueryTrajectory {
	
	 private Connection conn;
	   
	 public QueryTrajectory()throws Exception{
	    		
	    conn= DatabaseConn.getConnection();
	     
	    }
	   
	   //return the number of people
	   public int getMaxPeopleId() throws Exception{
	    	int maxId;
	    	Statement stm=conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
	    	ResultSet rs=stm.executeQuery("select Max(id) from People");
	    	while(rs.next()){
	    		maxId=rs.getInt(1);
	    		return maxId+1;
	    	}
	    	return 1;
	    }
	   
	   //return the buildings in certain type
	   //if string == null, that means all the building
	   public ArrayList<Trajectory> getBuildinginType(String type) throws Exception{
		   
		    ArrayList<Place> list =new ArrayList<Place>();
	    	Statement stm=conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
	    	ResultSet rs=stm.executeQuery("select b.buildingid, b.bname, b.function, b.type from building b where b.type == "+ type +"order by p.buildingid desc" );
	    	while(rs.next()){
	    		Place b=new Place();
	    		b.setBuildingid(rs.getInt(1));
	    		b.setName(rs.getString(2));
	    		b.setFunction(rs.getString(3));
	    		b.setType(rs.getString(4));
		    	list.add(b);
		    }
		    return list;
	    }
	   
	   //used to get groups information
	   public   ArrayList<People> searchManyPeople() throws Exception{
		   ArrayList<People> list =new ArrayList<People>();
			Statement stm=conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
	    	ResultSet rs=stm.executeQuery("select p.peopleid, p.name, p.age,p.occupation from test.people p order by p.peopleid desc ");
	    while(rs.next()){
	    	People m=new People();
	    	m.setPeopleID(rs.getInt(1));
	    	m.setName(rs.getString(2));
	    	m.setAge(rs.getInt(3));
	    	list.add(m);
	    }
	    return list;
	   } 
	   
	   // used to get people information !!!!!!!!!!!!!!!! not finish
	public People searchPeople(int peopleid) throws Exception {

		People p = new People();
		Statement stm = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
				ResultSet.CONCUR_UPDATABLE);
		ResultSet rs = stm
				.executeQuery("select p.name, p.age, p.occupation from test.people p where p.peopleid = "
						+ peopleid + ";");
		while (rs.next()) {
			p.setPeopleID(peopleid);
			p.setName(rs.getString(1));
			p.setAge(rs.getInt(2));
			p.setOccupationID(rs.getString(3));
		}
		return p;
	} 
	
	//used to get Peopleid by peoplename
	public People searchPeopleid(String name) throws Exception {

		People p = new People();
		Statement stm = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
				ResultSet.CONCUR_UPDATABLE);
		ResultSet rs = stm
				.executeQuery("select p.peopleid, p.age, p.occupation from test.people p where p.name = "
						+ name + ";");
		while (rs.next()) {
			p.setPeopleID(rs.getInt(1));
			p.setName(name);
			p.setAge(rs.getInt(2));
			p.setOccupationID(rs.getString(3));
		}
		return p;
	} 
	
	 // used to get people information !!!!!!!!!!!!!!!! not finish
	public Trajectory searchTrajectory(int peopleid) throws Exception {

		Trajectory p = new Trajectory();
		p.setPeople(this.searchPeople(peopleid));
		p.setStops(this.searchStops(peopleid));
		p.setMoves(this.searchMoves(peopleid));
		p.setEvents(this.searchEvents(peopleid));

		return p;
	} 
	
	//search trajectory which contains this kind of events
	public ArrayList<Trajectory> searchTrajectory(String type) throws Exception{
		ArrayList<Trajectory> trajectorylist = new ArrayList<Trajectory>();
		
		ArrayList<Event> eventlist = this.searchEvent(type);
	
		int[] peopleid = new int[50];
		for(int i = 0; i< eventlist.size(); i++)
		{
			int j = 0;
			int k = 0;
			while(peopleid[k]>0)
			{
				if(peopleid[k] == eventlist.get(i).getPeople().get(0).getPeopleId())
				{
					j = 1;
					break;
				}
				k++;
			}
			if(j == 0)
			{
			  int h = 0;
			  while(peopleid[h]!=0)
			  {
				  h++;
			  }
			  
			  peopleid[h] = eventlist.get(i).getPeople().get(0).getPeopleId();
			}
		}
		
		int j = 0;
		while(peopleid[j]>0)
		{
			Trajectory t = this.searchTrajectory(peopleid[j]);
			trajectorylist.add(t);
			j++;
		}
		
		return trajectorylist;
	}
	
	
	//search meet relationship with event id
	public ArrayList<SpatiotempralMeet> SearchEventMeetByevent(int[] eventid) throws Exception{
		
		ArrayList<SpatiotempralMeet> meetlist = new ArrayList<SpatiotempralMeet>();
		
		//second step query out the number;
		int k = 0;
		while(eventid[k]!=0)
		{
			//decide whether this events belongs to different stops, which is a meet;
			Statement stm = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
			ResultSet rs = stm.executeQuery("select count(*) from test.event e where e.eventid =" + eventid[k] );// + "order by e.id desc "
			int number = 0;
			while (rs.next()) {
			   number = rs.getInt(1);
			}
			
			if(number > 1)
			{
				SpatiotempralMeet meet = new SpatiotempralMeet();
				EventEx e = this.searchEventEx(eventid[k]);
				meet.setEvent(e);
				meet.setPlace(e.getPlace());
				meetlist.add(meet);
			}
			k++;

		}
		return meetlist;
		
	}
	
	//search meet relationship with people id
	public ArrayList<SpatiotempralMeet> SearchEventMeetBypeople(int[] peopleid) throws Exception{
	   
		ArrayList<SpatiotempralMeet> meetlist = new ArrayList<SpatiotempralMeet>();
		
		//first step query out eventid
		int[] eventid = new int[50];
		
		int i = 0;
		while (peopleid[i] != 0) {
			
			ArrayList<Event> elist = this.searchEvents(peopleid[i]);

			for (int h = 0; h < elist.size(); h++) {
				int find = 0;
				int j = 0;
				while (eventid[j] != 0) {
					
					if (eventid[j] == elist.get(h).getEventid()) {
						find = 1;
						break;
					}
					j++;
				}
				if (find == 0) {
					eventid[j] = elist.get(h).getEventid();
					
				}
			}
			i++;
		}
		
		//second step query out the number;
		int k = 0;
		while(eventid[k]!=0)
		{
			//decide whether this events belongs to different stops, which is a meet;
			Statement stm = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
			ResultSet rs = stm.executeQuery("select count(*) from test.event e where e.eventid =" + eventid[k] );// + "order by e.id desc "
			int number = 0;
			while (rs.next()) {
			   number = rs.getInt(1);
			}
			
			if(number > 1)
			{
				SpatiotempralMeet meet = new SpatiotempralMeet();
				EventEx e = this.searchEventEx(eventid[k]);
				meet.setEvent(e);
				meet.setPlace(e.getPlace());
				meetlist.add(meet);
			}
			k++;

		}
		return meetlist; 
	}
	
	
	public EventEx searchEventEx(int eventid) throws Exception{
		
		EventEx ex = new EventEx();
		ex.setEventid(eventid);
		
		Statement stm = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
		ResultSet rs = stm.executeQuery("select e.peopleid, e.stopid, e.eventType, e.eventInfo from test.event e where e.eventid = " + eventid);// + "order by e.id desc "
		
		ArrayList<People> plist = new ArrayList<People>();
		ArrayList<Stop> slist = new ArrayList<Stop>();
		while (rs.next()) {
			People p = this.searchPeople(rs.getInt(1));
			plist.add(p);
			Stop s =this.searchStop(rs.getInt(2));
			slist.add(s);
			ex.setEventtype(rs.getString(3));
			ex.setEventinfo(rs.getString(4));
		}
		
		ex.setPeoples(plist);
		ex.setStops(slist);
		ex.setPlace(slist.get(0).getPlace());
		
		
		return ex;
		
	}
	
	
	//search event which belongs to one categories  
	public ArrayList<Event> searchEvent(String type) throws Exception {

		ArrayList<Event> eventlist = new ArrayList<Event>();
		Statement stm = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
				ResultSet.CONCUR_UPDATABLE);
		ResultSet rs = stm.executeQuery("select e.eventid, e.eventInfo,e.peopleid, e.stopid from test.event e where e.eventType = '" + type + "'");// + "order by e.id desc "
		
		while (rs.next()) {
			
			int id = rs.getInt(1);
			int right = 0;
			for(int i = 0;i<eventlist.size(); i++)
			{
				if(eventlist.get(i).getEventid() == id)
				{
					right = 1;
					break;
				}
			}
			
			if (right == 0) {
				Event e = new Event();
				int stopid = rs.getInt(4);
				Stop temps = searchStop(stopid);
				e.setStop(temps);
				int peopleid = rs.getInt(3);
				People p = this.searchPeople(peopleid); //
				ArrayList<People> pp = new ArrayList<People>();
				pp.add(p);
				e.setPeopleid(pp);
				e.setEventid(rs.getInt(1));
				e.setEventinfo(rs.getString(2));

				eventlist.add(e);
			}
		}

		return eventlist;
	}
	
	//used to get the nearBy place !!
	public ArrayList<SpatiotempralNear> searchNearPlaceBypoint(ArrayList<Event> p) throws Exception{
		
		ArrayList<SpatiotempralNear> nearlist = new ArrayList<SpatiotempralNear>();
		
		for(int i = 0; i < p.size(); i++)
		{
			SpatiotempralNear near = new SpatiotempralNear();
			
			ArrayList<Place> placelist = new ArrayList<Place>();
			Statement stm = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
					ResultSet.CONCUR_UPDATABLE);
			ResultSet rs = stm
			.executeQuery("select p.placeid, p.name, p.locationx, p.locationy, p.type,p.function from test.place p order by p.placeid asc ");
			
			while (rs.next()) {
			//now this is a judgement to find out whethr it is a near place
			
			double temx = rs.getDouble(3);
			double temy = rs.getDouble(4);
			
			double result = (temx - p.get(i).getStop().getPlace().getLocation().x)*(temx - p.get(i).getStop().getPlace().getLocation().x)+(temy - p.get(i).getStop().getPlace().getLocation().y)*(temy - p.get(i).getStop().getPlace().getLocation().y);
			if(result < 20)
			{
				Place temp = new Place();
				temp.setBuildingid(rs.getInt(1));
				temp.setName(rs.getString(2));
				temp.setLocation(new Vector3f((float)(rs.getDouble(3)),(float)(rs.getDouble(4)),0));
				temp.setType(rs.getString(5));
				temp.setFunction(rs.getString(6));
				
				placelist.add(temp);
			}
				
			
			
			}
			near.setPlaelist(placelist);
			near.setCenterEvent(p.get(i));
			nearlist.add(near);
			
		}
		
		return nearlist;
	}
	
	 // used to get place information !!!
	public Place searchPlace(int placeid) throws Exception {
		Place p = new Place();
		Statement stm = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
				ResultSet.CONCUR_UPDATABLE);

		ResultSet rs = stm
				.executeQuery("select p.name, p.locationx, p.locationy, p.type,p.function from test.place p where p.placeid = "
						+ placeid + " order by p.placeid desc ");
		while (rs.next()) {
			p.setBuildingid(placeid);
			p.setName(rs.getString(1));

			p.setLocation(new Vector3f((float) (rs.getDouble(2)), (float) (rs
					.getDouble(3)), 0.0f));

			p.setType(rs.getString(4));
			p.setFunction(rs.getString(5));
		}

		return p;
	} 
	 
	
	
	
	   // used to get place information !!!
	public ArrayList<Place> searchPlaces(String placeType) throws Exception {

		ArrayList<Place> places = new ArrayList<Place>();

		Statement stm = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
				ResultSet.CONCUR_UPDATABLE);
		ResultSet rs = null;
		if(placeType.equalsIgnoreCase("all")){
			rs = stm.executeQuery(" select p.placeid, p.name, p.locationx, p.locationy, p.function, p.type from test.place p order by p.placeid desc");
				
		}else{
		
			rs = stm.executeQuery(" select p.placeid, p.name, p.locationx, p.locationy, p.function, p.type from test.place p where p.type = '"+ placeType + "' order by p.placeid desc");
		}// 

		while (rs.next()) {
			Place p = new Place();
			p.setBuildingid(rs.getInt(1));
			p.setName(rs.getString(2));
		 //   java.sql.
			//PointFromText(rs.getString(3));
		//	POINT P = new POINT();
			
			p.setLocation(new Vector3f((float)rs.getDouble(3),(float)rs.getDouble(4), 0.5f));

			if(placeType.equalsIgnoreCase("all"))
			{
				p.setType(rs.getString(6));
			}else{
				p.setType(placeType);
			}
			
			p.setFunction(rs.getString(5));
			places.add(p);
		}
		return places;
	} 
	   
	   // used to get the stops of one people
	   public   ArrayList<Stop> searchStops(int peopleid) throws Exception{
		    ArrayList<Stop> list =new ArrayList<Stop>();
			Statement stm=conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
	    	ResultSet rs=stm.executeQuery("select s.stopid, s.placeid, s.locationx,s.locationy, s.Btime, s.Etime from test.stop s where s.peopleid = "+ peopleid +"  order by s.stopid asc ");
	    while(rs.next()){
	    	Stop s=new Stop();
	    	s.setStopid(rs.getInt(1));
	    	s.setPeople(peopleid);
	    	s.setBuildingid(rs.getInt(2));
	   // 	s.setBeginTime(rs.getDate(5));
	    	s.setEndTime(rs.getTimestamp(6));  	
	    	s.setBeginTime(rs.getTimestamp(5));
	    	System.out.print(s.getBeginTime().toString());
	    	s.setPlace(searchPlace(s.getBuilding()));
	    	s.setPosition(s.getPlace().getLocation());
	    	list.add(s);
	    }
	    return list;
	   } 
	   
	   
	   //used to get the stoplist happened on certain place
	   public ArrayList<Stop> searchStoplist(int placeid) throws Exception{
		   ArrayList<Stop> stoplist = new ArrayList<Stop>();
		   Statement stm=conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
		   ResultSet rs = stm
					.executeQuery("select s.stopid, s.peopleid, s.locationx,s.locationy, s.Btime, s.Etime from test.stop s where s.placeid = "
							+ placeid); //+ "  order by s.time desc 
		   while (rs.next()) {
			   Stop s = new Stop();
			   s.setBuildingid(placeid);
			   s.setStopid(rs.getInt(1));
			   s.setPeople(rs.getInt(2));
			   s.setPlace(searchPlace(placeid));
		       s.setPosition(s.getPlace().getLocation());
		       s.setBeginTime(rs.getTimestamp(5));
		       s.setEndTime(rs.getTimestamp(6));
		       
		       stoplist.add(s);
		   }
		   
		   return stoplist;
	   }
	   
	   //used to get the stop with stopid 
	   public Stop searchStop(int stopid) throws Exception {
		Stop s = new Stop();
		Statement stm=conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
		ResultSet rs = stm
				.executeQuery("select s.peopleid, s.placeid, s.locationx,s.locationy, s.Btime, s.Etime from test.stop s where s.stopid = "
						+ stopid); //+ "  order by s.time desc 
		while (rs.next()) {

			s.setStopid(stopid);
	    	s.setPeople(rs.getInt(1));
	    	s.setBuildingid(rs.getInt(2));
	    	s.setBeginTime(rs.getTimestamp(5));
	    	System.out.println(s.getBeginTime().toString());
	    	s.setEndTime(rs.getTimestamp(6));  	
	    	
	    	s.setPlace(searchPlace(s.getBuilding()));
	    	s.setPosition(s.getPlace().getLocation());
		}
		return s;
	} 
	   
	   // used to get the moves of one people
	   public   ArrayList<Move> searchMoves(int peopleid) throws Exception{
		    ArrayList<Stop> stoplist = searchStops(peopleid);
			ArrayList<Move> movelist = new ArrayList<Move>();
			
		    for (int i = 0; i< stoplist.size()-1; i++)
		    {
		       Move m = new Move();
		       m.setBeginStopid(stoplist.get(i).getStopid());
		       m.setEndStopid(stoplist.get(i+1).getStopid());
		       m.setBeginStop(stoplist.get(i).getPostion());
		       m.setEndStop(stoplist.get(i+1).getPostion());
		       m.setBeginTime(stoplist.get(i).getEndTime());
		       m.setEndTime(stoplist.get(i+1).getBeginTime());
		       movelist.add(m);
		    }
		    
	    return movelist;
	   } 
	   
	   /**
	    * 	private int m_eventid;
	private int m_stopid;
	private Stop m_stop;
//	private int m_peopleid;
	private int peoples[];
	private String m_eventinfo;
	private String m_eventType;   //
	private java.sql.Date m_BeginTime;
	private java.sql.Date m_EndTime;
	    
	    * @param peopleid
	    * @param time
	    * @return
	    * @throws Exception
	    */
	   //used to get the events of one people 
	   public ArrayList<Event> searchEvents(int[] peopleid, java.util.Date time)
			throws Exception {
		ArrayList<Event> eventlist = new ArrayList<Event>();
		int i = 0;
		while (peopleid[i] > 0) {
			Event e = new Event();
			Event bs = new Event();
			Statement stm=conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
			ResultSet rs = stm.executeQuery("select e.eventid, e.eventInfo,e.eventType, e.stopid from test.event e where e.peopleid = "+ peopleid[i]); //+ "order by e.stopid desc "

			while (rs.next()) {
				int stopid = rs.getInt(4);
				Stop temps = searchStop(stopid);
				System.out.print(temps.getBeginTime().toString());
				System.out.print(temps.getEndTime().toString());
				System.out.print(time.toString());
				int b = 0;
				if ((temps.getBeginTime().before(time)||temps.getBeginTime().equals(time))&&(temps.getEndTime().after(time)||temps.getEndTime().equals(time))
						&& (temps.getEndTime().after(time))) {
					e.setStop(temps);
					e.setEventid(rs.getInt(1));
					e.setEventinfo(rs.getString(2));
					e.setEventtype(rs.getString(3));
				    ArrayList<People> pp = new ArrayList<People>();
				    pp.add(this.searchPeople(peopleid[i]));
				    e.setPeopleid(pp);
				    

					eventlist.add(e);
					i++;
					break;

				} else if (temps.getEndTime().before(time)) {

					bs.setStop(temps);
					bs.setEventid(rs.getInt(1));
					bs.setEventinfo(rs.getString(2));
					bs.setEventtype(rs.getString(3));
					b = 1;

				} else if (temps.getBeginTime().after(time)) {
					if (b == 1) {
						eventlist.add(bs);
						e.setStop(temps);
						e.setEventid(rs.getInt(1));
						e.setEventinfo(rs.getString(2));
						e.setEventtype(rs.getString(3));

						eventlist.add(e);
						i++;
						break;
					}
				}
				i++;

			}
		}
		return eventlist;
	} 
	   
	   // used to get the events of one people
	public ArrayList<Event> searchEvents(int peopleid) throws Exception {

		ArrayList<Event> eventlist = new ArrayList<Event>();
		Statement stm=conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
		ResultSet rs = stm
				.executeQuery("select e.eventid, e.eventInfo,e.eventType, e.stopid from test.event e where e.peopleid = "
						+ peopleid );//+ "order by e.id desc "

		while (rs.next()) {

			Event e = new Event();

			int stopid = rs.getInt(4);
			Stop temps = searchStop(stopid);
			e.setStop(temps);
			e.setEventid(rs.getInt(1));
			e.setEventinfo(rs.getString(2));
			e.setEventtype(rs.getString(3));

			eventlist.add(e);
		}
		
		return eventlist;

	} 
	
	
	public ArrayList<Event> searchEvent(int[] m_jpeopleid, String type)throws Exception {
		// TODO Auto-generated method stub
		ArrayList<Event> eventlist = new ArrayList<Event>();
		
		Statement stm = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
		ResultSet rs = null; 
		if(type.equalsIgnoreCase("all"))
		{
			rs = stm.executeQuery("select e.eventid, e.eventInfo,e.peopleid, e.stopid, e.eventType from test.event e"); // + "order by e.id desc "
		}else{
			rs = stm.executeQuery("select e.eventid, e.eventInfo,e.peopleid, e.stopid,e.eventType from test.event e where e.eventType = '" + type + "'");// + "order by e.id desc "
		}
		while (rs.next()) {
			
			int right = 0;
			for(int i = 0;m_jpeopleid[i]!=0; i++)
			{
				if(rs.getInt(3) == m_jpeopleid[i])
				{
					right = 1;
					break;
				}
			}
			
			if (right == 1) {
				Event e = new Event();
				int stopid = rs.getInt(4);
				Stop temps = searchStop(stopid);
				e.setStop(temps);
				int peopleid = rs.getInt(3);
				People p = this.searchPeople(peopleid); //
				ArrayList<People> pp = new ArrayList<People>();
				pp.add(p);
				e.setPeopleid(pp);
				e.setEventid(rs.getInt(1));
				e.setEventinfo(rs.getString(2));
				if(type.equalsIgnoreCase("all"))
				{
					e.setEventtype(rs.getString(5));
				}else{
					e.setEventtype(type);
				}
				eventlist.add(e);
			}
		}

	
		return eventlist;
	}
	   
	public ArrayList<Event> searchEvent(int[] m_jpeopleid, String type, float[] during)throws Exception {
		// TODO Auto-generated method stub
		ArrayList<Event> eventlist = new ArrayList<Event>();
		Statement stm = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
		ResultSet rs = null;
		if(type.equalsIgnoreCase("all"))
		{
			rs = stm.executeQuery("select e.eventid, e.eventInfo,e.peopleid, e.stopid, e.eventType from test.event e");// + "order by e.id desc "

		}else{
			rs = stm.executeQuery("select e.eventid, e.eventInfo,e.peopleid, e.stopid, e.eventType from test.event e where e.eventType = '" + type + "'");// + "order by e.id desc "
	
		}
		
		while (rs.next()) {
			
			int right = 0;
			for(int i = 0;m_jpeopleid[i]!=0; i++)
			{
				if(rs.getInt(3) == m_jpeopleid[i])
				{
					
					right = 1;
					
					break;
				}
			}
			
			if (right == 1) {
				Event e = new Event();
				int stopid = rs.getInt(4);
				Stop temps = searchStop(stopid);
				e.setStop(temps);
				int peopleid = rs.getInt(3);
				People p = this.searchPeople(peopleid); //
				ArrayList<People> pp = new ArrayList<People>();
				pp.add(p);
				e.setPeopleid(pp);
				e.setEventid(rs.getInt(1));
				e.setEventinfo(rs.getString(2));
				if(type.equalsIgnoreCase("all"))
				{
					e.setEventtype(rs.getString(5));
				}else{
					e.setEventtype(type);
				}
				
				
				if((during[0]<=e.getDuring())&&(during[1]>e.getDuring()))
				{
					eventlist.add(e);
				}
				
			}
		}

	
		return eventlist;
	}
	   // used to calculate the times of people interested in one place
	   public int[] calPlaceVisitTimes(String placeType) throws Exception
	   {
		    ArrayList<Place> list = getBuildinginType(placeType);
		    int n = list.size();
		    int visitNum[] = null; //old is id, even is times
		    for(int i = 0; i <n*2 ; i = i+2)
		    {
		    	visitNum[i] = list.get(i).getBuildingid();
		    	visitNum[i+1] = 0;
		    }
		    
			Statement stm=conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
			 
			for(int i = 0; i <n*2 ; i = i+2)
			{
		    	
			   ResultSet rs=stm.executeQuery("select count(s.stopid) from trajectory.stops s where s.buildingid = "+ visitNum[i]);			 
			   visitNum[i+1] = rs.getInt(1);
			 }
			   
		return visitNum;
		   
	   }
	   
	 //used to calculate the number of traffic in one place at a certain time
	   public int calVisitTimes(int buildingid, java.sql.Date dateB, java.sql.Date dateE) throws Exception
	   {
		   
			Statement stm=conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
			ResultSet rs=stm.executeQuery("select count(s.stopid) from trajectory.stops s where s.buildingid = "+ buildingid + "and time > " + dateB + "and time <" + dateE);			 		      
		return rs.getInt(1);
		   
	   }

	
	   
	  
	   /**
	   public void addMeeting(Meeting m) throws Exception{
		   m.setMeetingId(this.getMaxMeetingId());
		//   System.out.print(m.getUserId());
		   PreparedStatement pstmt =conn.prepareStatement("insert  into oa_meeting values(?,?,?,?,TO_Date('"+m.getStartTime()+"','yyyy-mm-dd hh24:mi:ss'),?,empty_blob(),TO_Date('"+m.getEndTime()+"','yyyy-mm-dd hh24:mi:ss'))" );
	    	pstmt.setInt(1, this.getMaxMeetingId());
	    	pstmt.setInt(2, m.getRoomId());
	    	pstmt.setInt(3,m.getUserId());
	    	pstmt.setString(4, m.getTheme());
	    	pstmt.setString(5, m.getContent());
	         pstmt.execute();
	         addUser(m.getUserId(),m) ;
		   
	   }
	   public void addUser(int id,Meeting m) throws Exception{
		   Statement stm=conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
		
	    	ResultSet rs=stm.executeQuery("insert into oa_querymeeting values("+this.getMaxqueryMeetingId()+","+m.getMeetingId()+","+id+")");
		  
	   }
	   public void editMeeting(Meeting m) throws Exception{
		   PreparedStatement pstmt =conn.prepareStatement("update oa_meeting set theme=?," +
           "starttime=TO_Date('"+m.getStartTime()+"','yyyy-mm-dd hh24:mi:ss'),endtime=TO_Date('"+m.getEndTime()+"','yyyy-mm-dd hh24:mi:ss')"+
           ",roomid=?, content=? where meetingid=?");
            
            pstmt.setString(1,m.getTheme());
            pstmt.setInt(2,m.getRoomId());
            pstmt.setString(3, m.getContent());
            pstmt.setInt(4,m.getMeetingId());
            pstmt.execute();
         this.Updateroomname(m);
	   }
	   public ArrayList<String> getUsersName(Meeting m) throws SQLException{
		   ArrayList<String> list= new ArrayList<String>();
		   Statement stm=conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
		   ResultSet rs=stm.executeQuery("select username from oa_user where userid in(select userid from oa_querymeeting where meetingid="+m.getMeetingId()+")");
		   while(rs.next()){
			   list.add(rs.getString(1));
		   }
		   return list;
	   } 
	   public Meeting getMeeting(int meetingid) throws SQLException{
		   
		   Meeting m= new Meeting();
			Statement stm=conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
	    	ResultSet rs=stm.executeQuery("select m.meetingid, m.roomid, m.theme, TO_CHAR(m.starttime,'yyyy-mm-dd hh24:mi:ss'),TO_CHAR(m.endtime,'yyyy-mm-dd hh24:mi:ss'),m.userid,m.content, mr.roomname from oa_meeting m ,oa_meetingroom mr where m.meetingid ="+meetingid+" and mr.roomid=m.roomid  ");
	       while(rs.next()){
	    	m.setMeetingId(rs.getInt(1));
	    	m.setRoomId(rs.getInt(2));
	    	m.setTheme(rs.getString(3));
	    	m.setStartTime(rs.getString(4));
	    	m.setEndTime(rs.getString(5));
	    	m.setUserId(rs.getInt(6));
	    	m.setContent(rs.getString(7));
	    	m.setRoomName(rs.getString(8));
	       }
	       return m;
	   }
	 public String getNames(Meeting m) throws Exception{
		 ArrayList<String> list= this.getUsersName(m);
		 String names="";
		 for(int i=0;i<list.size()-1;i++){
			 names=names+list.get(i)+",";
		 }
		 names=names+list.get(list.size()-1);
		 return names;
	 }
	 public void deleteQueryMeeting(int meetingid) throws Exception{
	    	PreparedStatement pstmt =conn.prepareStatement("delete  from oa_querymeeting where meetingid="+meetingid);
	      
	    	pstmt.execute();
	    	
	    }
	  public void deleteMeeting(int id) throws Exception{
		  this.deleteQueryMeeting(id);
	    	PreparedStatement pstmt =conn.prepareStatement("delete  from oa_meeting where meetingid="+id);
	      
	    	pstmt.execute();
	    	
	    }
		 public void Updateroomname(Meeting m) throws Exception{
				Statement stm=conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
				ResultSet rs=stm.executeQuery("select roomname from  oa_meetingroom where roomid="+m.getRoomId());
		     
		    rs.next();
		    m.setRoomName(rs.getString(1));
		    
		    }
		    */
	   
	   
}